﻿''' <summary>
''' 選擇采購申請單
''' </summary>
''' <remarks>
''' <list>Create Person:Huyang</list>
''' <list>Create Date:2007-08-27</list>
''' </remarks>
Public Class frm_SelectPurRequst

#Region "Variabled Define"

    Private fLoadBool As Boolean = False
    Private m_EffectDate As String

    ''' <summary>
    ''' 生效日期
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property EffectDate() As String
        Get
            Return m_EffectDate
        End Get
        Set(ByVal value As String)
            If IsDate(value) = True Then
                m_EffectDate = value
            Else
                m_EffectDate = ServerDate
            End If
        End Set
    End Property

    Private m_SuppCode As String

    ''' <summary>
    ''' 供應商
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property SuppCode() As String
        Get
            Return m_SuppCode
        End Get
        Set(ByVal value As String)
            m_SuppCode = value
        End Set
    End Property


#End Region

#Region "Methods Define"

#Region "Initialize"

    Private Function Re_Caption() As Hashtable
        Dim Hash As New Hashtable
        With Hash
            .Add("FNum", "申請單號")
            .Add("FDate", "申請日期")
            .Add("FDept", "申請部門")
            .Add("FEmployee", "申請人")
            .Add("SN", "序號")
            .Add("BarCode", "條碼")
            .Add("SupperItem", "産商編號")
            .Add("Cnname", "中文名稱")
            .Add("Enname", "英文名稱")
            .Add("ColorSName", "顔色")
            .Add("SizeSName", "尺寸")
            .Add("Code", "貨品編號")
            .Add("Unit", "單位")
            .Add("FNeedQty", "需求數量")
            .Add("FStoreQty", "庫存數量")
            .Add("FMinPOQty", "最低採購量")
            .Add("FPOQty", "採購數量")
            .Add("FPrice", "單價")
            .Add("FAmount", "金額")
            .Add("DeliveryDate", "期望貨期")
            .Add("FRemark", "備註")

        End With
        Return Hash
    End Function

    Private Sub loadCombobox()
        Try
            Dim sql As String = "SELECT fCode,fCName FROM BSC_DEPT WHERE fDefine=1 ORDER BY fCName " + vbNewLine + "SELECT fCode,fCName FROM BSC_Employee ORDER BY fCName"
            Dim ds As DataSet = Exec(sql, E_ExecType.DataSet)
            Me.cbxFDept.ValueMember = "fCode"
            Me.cbxFDept.DisplayMember = "fCName"
            Me.cbxFDept.DataSource = ds.Tables(0)
            Me.cbxFDept.Text = ""
            Me.cbxEmployee.ValueMember = "fCode"
            Me.cbxEmployee.DisplayMember = "fCode"
            Me.cbxEmployee.DataSource = ds.Tables(1)
            Me.cbxEmployee.Text = ""
        Catch ex As Exception
            MessageBox.Show(ex.ToString, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

#End Region

#Region "Exec Find"

    ''' <summary>
    ''' 返回查詢條件
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function Re_WhereExp() As String
        Dim Sql As String = ""
        If Me.fLoadBool = False Then
            Sql = " where 1<>1"
        Else
            Sql = " where 1=1"
            If Me.txtFNum.Text.Trim <> "" Then
                Sql &= " and FNum like '%" & Replace_Text(Me.txtFNum.Text.Trim) & "%'"
            End If
            If Me.cbxFDept.Text.Trim <> "" And Not Me.cbxFDept.SelectedValue Is Nothing Then
                Sql &= " and FDept='" & Me.cbxFDept.SelectedValue & "'"
            End If
            If Me.cbxEmployee.Text.Trim <> "" Then
                Sql &= " and FEmployee like '%" & Replace_Text(Me.cbxEmployee.Text.Trim) & "%'"
            End If
            If Me.CheckBox1.Checked = True Then
                Sql &= " and Convert(varchar(10),FDate,21)='" & Me.dptFDate.Text & "'"
            End If
            'If Me.txtFEmployee.Text.Trim <> "" Then
            '    Sql &= " and FEmployee like '%" & Replace_Text(Me.txtFEmployee.Text.Trim) & "%'"
            'End If

            If Me.txtBarCode.Text.Trim <> "" Then
                Sql &= " and b.BarCode like '%" & Replace_Text(Me.txtBarCode.Text.Trim) & "%'"
            End If
            If Me.CheckBox2.Checked = True Then
                Sql &= " and Convert(varchar(10),DeliveryDate,21)='" & Me.dtpDeliveryDate.Text & "'"
            End If
            If Me.txtSupperItem.Text.Trim <> "" Then
                Sql &= " and c.SupperItem like '%" & Replace_Text(Me.txtSupperItem.Text.Trim) & "%'"
            End If
            If Me.txtCnname.Text.Trim <> "" Then
                Sql &= " and c.Cnname like '" & Replace_Text(Me.txtCnname.Text.Trim) & "'"
            End If
            Sql &= "  and isnull(a.BillstatusId,'')='B' "
        End If
        Return Sql
    End Function

    ''' <summary>
    ''' 執行查詢過程
    ''' </summary>
    ''' <param name="WhereExp"></param>
    ''' <remarks></remarks>
    Private Sub ExecFind(ByVal WhereExp As String)
        Try

            Dim sql As String = ""

            ''最新報價
            Dim Table As String = System.Guid.NewGuid().ToString
            sql &= " select Distinct a.Num,ValidDate,SuppCode,BillstatusId," & vbCrLf
            sql &= " BarCode,Price Into [#" & Table & "]" & vbCrLf
            sql &= " from Pur_QuoteA a left join Pur_QuoteB b on a.Num=b.Num" & vbCrLf
            sql &= " where Isnull(BillstatusId,'')='B' and Convert(varchar(10),ValidDate,21)<='" & Me.EffectDate & "'" & vbCrLf
            sql &= " and SuppCode='" & SuppCode & "'" & vbCrLf

            sql &= " select Distinct a.ID,FNum,FDate=Convert(varchar(10),FDate,21),r.fCName as FDept,FEmployee,SN,BarCode=Replace(b.BarCode,'*',''),c.Code,c.SupperItem,c.Cnname,c.Enname,ColorSName,SizeSName,c.Unit," & vbCrLf
            sql &= " FNeedQty, FStoreQty, b.FMinPOQty, FPOQty, Case when Isnull(f.Price,0)=0 then FPrice else f.Price end as FPrice, b.FAmount, DeliveryDate=Convert(varchar(10),DeliveryDate,21), b.FRemark" & vbCrLf
            sql &= " from PUR_RequestA a inner join PUR_RequestB b on a.Id=b.Id" & vbCrLf
            sql += " left join BSC_DEPT r on a.fDept=r.fCode " + vbNewLine
            sql &= " left join BSC_Goods c on b.BarCode=c.BarCode" & vbCrLf
            sql &= " left join BSC_ColorDefine d on c.color=colorCode" & vbCrLf
            sql &= " left join BSC_SizeDefine e on c.Spec=e.SizeCode" & vbCrLf
            'sql &= " left join (" & vbCrLf
            sql &= " inner join (" & vbCrLf
            sql &= " select Num,ValidDate,BarCode,Price from [#" & Table & "] a where"
            sql &= " ValidDate=(select Max(ValidDate) from [#" & Table & "] c  where a.BarCode=c.BarCode)"
            sql &= "  ) f on b.BarCode=f.BarCode" & vbCrLf

            sql &= " " & WhereExp & vbCrLf

            sql &= " Drop table [#" & Table & "]" & vbCrLf

            Dim Dtab As New DataTable
            Dtab = Exec(sql, E_ExecType.DataTable)
            Me.DGView1.DataSource = Dtab
            BindDataGridViewCaption(Me.DGView1, Re_Caption())
            FullFormatDataGridView(DGView1)
            DGView1.MultiSelect = True
        Catch ex As Exception
            MessageBox.Show("ExecFind:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK)
        End Try
    End Sub

#End Region

#Region "AddHandler Events"

    ''' <summary>
    ''' KeyDown Event
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub TextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
        Try
            Select Case e.KeyCode
                Case Keys.Enter
                    If e.Shift = True Then
                        If TypeOf sender Is ComboBox Then
                            If CType(sender, ComboBox).DroppedDown = True Then
                                CType(sender, ComboBox).DroppedDown = False
                            End If
                        End If
                        SendKeys.Send("+{Tab}")
                        SendKeys.Send("{F4}")
                    Else
                        If TypeOf sender Is ComboBox Then
                            If CType(sender, ComboBox).DroppedDown = True Then
                                CType(sender, ComboBox).DroppedDown = False
                            End If
                        End If
                        SendKeys.Send("{Tab}")
                        SendKeys.Send("{F4}")
                    End If
            End Select
        Catch ex As Exception
            MsgBox("TextBox_KeyDown:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 添加自定義事件
    ''' </summary>
    ''' <param name="Pans"></param>
    ''' <remarks></remarks>
    Private Sub AddHandlers(ByVal Pans() As Control)
        Try
            For Each Pan As Control In Pans
                For Each Ctrl As Control In Pan.Controls
                    If TypeOf Ctrl Is TextBox _
                       OrElse TypeOf Ctrl Is ComboBox _
                       OrElse TypeOf Ctrl Is DateTimePicker _
                       OrElse TypeOf Ctrl Is RadioButton Then
                        AddHandler Ctrl.KeyDown, AddressOf TextBox_KeyDown
                    End If
                Next
            Next
        Catch ex As Exception
            MsgBox("AddHandlers:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub


#End Region

#End Region

#Region "Controls Define"


    ''' <summary>
    ''' 窗體加載
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub frm_SelectPurRequst_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Me.fLoadBool = False
            AddHandlers(New Control() {Me.Panel2})
            loadCombobox()
            Dim WhereExp As String = Me.Re_WhereExp()
            ExecFind(WhereExp)
            Me.fLoadBool = True
        Catch ex As Exception
            MessageBox.Show("frm_SelectPurRequst_Load:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK)
        End Try
    End Sub

    ''' <summary>
    ''' 查詢事件
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelect.Click
        Dim whereExp As String = Me.Re_WhereExp()
        ExecFind(whereExp)
    End Sub

    Private Sub btnOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOK.Click
        Me.DialogResult = Windows.Forms.DialogResult.OK
    End Sub

    Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = Windows.Forms.DialogResult.Cancel
    End Sub

    Private Sub DGView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGView1.CellDoubleClick
        Me.DialogResult = Windows.Forms.DialogResult.OK
    End Sub

#End Region


End Class